﻿Imports System.Data.OleDb
Imports System.Data.SqlClient


Partial Class Autos
    Inherits System.Web.UI.Page
    Private db As Object, rs As Object
    Private x As Integer
    Private Cnx As OleDbConnection, Sql As String
    Private Cmd As OleDbCommand
    Private Dset As Data.DataSet
    Private Dadap As OleDbDataAdapter
    Private base As String

    Protected Sub btnAgregar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAgregar.Click
        PanelSeleccion.Visible = False
        PanelFiltro.Visible = False
        PanelEliminar.Visible = False
        PanelEdicion.Visible = True
        ViewState("Accion") = "Agregar"
        LimpiarPanelEdicion()
    End Sub
    Protected Sub btnEditar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEditar.Click
        PanelSeleccion.Visible = True
        PanelFiltro.Visible = False
        PanelEliminar.Visible = False
        PanelEdicion.Visible = False
        ViewState("Accion") = "Editar"
    End Sub
    Protected Sub btnEliminar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEliminar.Click
        PanelSeleccion.Visible = False
        PanelFiltro.Visible = False
        PanelEliminar.Visible = True
        PanelEdicion.Visible = False
        ViewState("Accion") = "Eliminar"
    End Sub
    Protected Sub btnFiltrar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFiltrar.Click
        PanelSeleccion.Visible = False
        PanelFiltro.Visible = True
        PanelEliminar.Visible = False
        PanelEdicion.Visible = False
        ViewState("Accion") = "Filtrar"
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        '"<bgsound src="C:\WebSira\Sonidos\Autos.wav" loop=false volume=10>

        base = "c:\WebSira\Sira.mdb"
        Cnx = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;Data Source=" & base & ";")
        Dadap = New OleDbDataAdapter("SELECT * FROM Autos ORDER BY Patente", Cnx)
        Dset = New Data.DataSet()
        Dadap.Fill(Dset, "MiTabla")
        GV2.DataSource = Dset
        GV2.DataMember = "MiTabla"
        GV2.DataBind()
        Cnx.Close()
        If Not IsPostBack Then
            LlenarDDLAños()
            LlenarDDLCampos()
        End If
        lblUser.Text = Session("VarSessionUser")
    End Sub

    Protected Sub LlenarDDLAños()
        For x = Year(Today()) - 10 To Year(Today())
            DDLAños.Items.Add(x)
        Next
    End Sub

    Protected Sub LlenarDDLCampos()
        db = Server.CreateObject("ADODB.Connection")
        rs = Server.CreateObject("ADODB.Recordset")
        Try
            db.open("Provider=Microsoft.jet.oledb.4.0;Data Source=" & base & ";")
            rs.open("SELECT * FROM Autos ORDER BY Patente", db)
            DDLCampos.ClearSelection()
            For x = 0 To rs.Fields.Count - 1
                DDLCampos.Items.Add(rs.Fields.Item(x).Name)
            Next
        Catch ex As Exception
            MsgBox("No puedo" & vbCrLf & ex.Message)
        End Try
        rs.close()
        db.close()
        db = Nothing
        rs = Nothing
    End Sub

    Protected Sub LimpiarPanelEdicion()
        txtPatente.Text = ""
        txtMarca.Text = ""
        txtModelo.Text = ""
        txtTitular.Text = ""
        txtAño.Text = ""
        txtComisaria.Text = ""
        txtEstado.Text = ""
        DDLMarcas.SelectedIndex = 0
        DDLModelos.SelectedIndex = 0
        DDLTitulares.SelectedIndex = 0
        DDLAños.SelectedIndex = 0
        DDLComisarias.SelectedIndex = 0
        DDLEstados.SelectedIndex = 0
    End Sub
    Protected Sub DDLMarcas_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLMarcas.SelectedIndexChanged
        If DDLMarcas.SelectedValue = "Su Elección" Then
            Exit Sub
        End If
        db = Server.CreateObject("ADODB.Connection")
        rs = Server.CreateObject("ADODB.Recordset")
        Try
            db.open("Provider=Microsoft.jet.oledb.4.0;Data Source=" & base & ";")
            rs.open("SELECT Modelo FROM Modelos WHERE Marca = '" & DDLMarcas.SelectedValue & "' ORDER BY Modelo", db)
            DDLModelos.Items.Clear()
            DDLModelos.Items.Add("Su Elección")
            rs.movefirst()
            Do While Not rs.eof
                DDLModelos.Items.Add(rs.fields(0).value)
                rs.movenext()
            Loop
        Catch ex As Exception
            MsgBox("No puedo abrir recordset = SELECT Modelo FROM Modelos WHERE Marca = '" & DDLMarcas.SelectedValue & "'" & vbCrLf & ex.Message, MsgBoxStyle.Critical, "Error proceso DDLMarcas_SelectedIndexChanged - Autos.aspx")
        End Try
        rs.close()
        db.close()
        db = Nothing
        rs = Nothing
        txtMarca.Text = DDLMarcas.SelectedValue
    End Sub

    Protected Sub DDL_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles DDLModelos.SelectedIndexChanged, DDLAños.SelectedIndexChanged, DDLTitulares.SelectedIndexChanged, _
            DDLComisarias.SelectedIndexChanged, DDLEstados.SelectedIndexChanged
        If sender.SelectedValue = "Su Elección" Then
            Exit Sub
        End If
        Select Case sender.id
            Case "DDLModelos" : txtModelo.Text = DDLModelos.SelectedValue
            Case "DDLAños" : txtAño.Text = DDLAños.SelectedValue
            Case "DDLTitulares" : txtTitular.Text = DDLTitulares.SelectedValue
            Case "DDLComisarias" : txtComisaria.Text = DDLComisarias.SelectedValue
            Case "DDLEstados" : txtEstado.Text = DDLEstados.SelectedValue
        End Select
    End Sub


    Protected Sub btnAceptar_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles btnAceptarEdicion.Click, btnAceptarPanelEliminar.Click
        Cnx = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;Data Source=" & base & ";")
        Select Case ViewState("Accion")
            Case "Editar"
                Try
                    Cnx.Open()
                    Sql = "UPDATE Autos set " & _
                          "Patente='" & txtPatente.Text & "'," & _
                          "Marca='" & txtMarca.Text & "'," & _
                          "Modelo='" & txtModelo.Text & "'," & _
                          "Año='" & txtAño.Text & "'," & _
                          "Titular='" & txtTitular.Text & "'," & _
                          "Comisaria='" & txtComisaria.Text & "'," & _
                          "Estado='" & txtEstado.Text & _
                          "' WHERE Patente='" & ViewState("Patente") & "'"
                    Cmd = New OleDbCommand(Sql, Cnx)
                    Cmd.ExecuteNonQuery()
                    Dadap = New OleDbDataAdapter("SELECT * FROM Autos ORDER BY Patente", Cnx)
                    Dset = New Data.DataSet()
                    Dadap.Fill(Dset, "MiTabla")
                    GV2.DataSource = Dset
                    GV2.DataMember = "MiTabla"
                    GV2.DataBind()
                    Cnx.Close()
                Catch ex As Exception
                    MsgBox("No puedo Actualizar (UPDATE) tabla Autos " & vbCrLf & ex.Message, MsgBoxStyle.Critical, "Error proceso btnAceptar_Click panel Edicion - Autos.aspx")
                End Try
            Case "Eliminar"
                If DDLPatenteEliminar.SelectedValue = "Su Elección" Then
                    lblCrtlDDLPatenteEliminar.Visible = True
                    Exit Sub
                Else
                    lblCrtlDDLPatenteEliminar.Visible = False
                End If
                Try
                    Cnx.Open()
                    Sql = "DELETE FROM Autos WHERE Patente='" & DDLPatenteEliminar.SelectedValue & "'"
                    Cmd = New OleDbCommand(Sql, Cnx)
                    Cmd.ExecuteNonQuery()
                    Dadap = New OleDbDataAdapter("SELECT * FROM Autos ORDER BY Patente", Cnx)
                    Dset = New Data.DataSet()
                    Dadap.Fill(Dset, "MiTabla")
                    GV2.DataSource = Dset
                    GV2.DataMember = "MiTabla"
                    GV2.DataBind()
                    DDLPatenteEliminar.Items.Remove(DDLPatenteEliminar.SelectedItem)
                    DDLPatenteEliminar.SelectedIndex = 0
                    Cnx.Close()
                Catch ex As Exception
                    MsgBox("No puedo ejecutar : DELETE FROM Autos WHERE Patente='" & DDLPatenteEliminar.SelectedValue & "'" & vbCrLf & ex.Message, MsgBoxStyle.Critical, "Error proceso btnEliminar_Click - Autos.aspx")
                End Try
            Case "Agregar"
                Try
                    Cnx.Open()
                    Sql = "INSERT INTO Autos (Patente,Marca,Modelo,Año,Titular,Comisaria,Estado) VALUES (" & _
                          "'" & txtPatente.Text & "'," & _
                          "'" & txtMarca.Text & "'," & _
                          "'" & txtModelo.Text & "'," & _
                          "'" & txtAño.Text & "'," & _
                          "'" & txtTitular.Text & "'," & _
                          "'" & txtComisaria.Text & "'," & _
                          "'" & txtEstado.Text & "')"
                    Cmd = New OleDbCommand(Sql, Cnx)
                    Cmd.ExecuteNonQuery()
                    Dadap = New OleDbDataAdapter("SELECT * FROM Autos ORDER BY Patente", Cnx)
                    Dset = New Data.DataSet()
                    Dadap.Fill(Dset, "MiTabla")
                    GV2.DataSource = Dset
                    GV2.DataMember = "MiTabla"
                    GV2.DataBind()
                    Cnx.Close()
                Catch ex As Exception
                    MsgBox("No puedo Agregar (INSERT INTO) tabla Autos " & vbCrLf & ex.Message, MsgBoxStyle.Critical, "Error proceso btnAceptar_Click - Autos.aspx")
                End Try
                LimpiarPanelEdicion()
        End Select
    End Sub

    Protected Sub btnAbortar_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles btnSalir.Click, btnSalirPanelEdicion.Click, btnSalirPanelEliminar.Click, btnSalirPanelFiltro.Click
        PanelSeleccion.Visible = False
        PanelEdicion.Visible = False
        PanelEliminar.Visible = False
        PanelFiltro.Visible = False
        ViewState("Accion") = ""
    End Sub

    Protected Sub btnRecuperar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRecuperar.Click
        txtMarca.Text = ViewState("Marca")
        txtModelo.Text = ViewState("Modelo")
        txtAño.Text = ViewState("Año")
        txtTitular.Text = ViewState("Titular")
    End Sub

    Protected Sub DDLPatentes_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLPatentes.SelectedIndexChanged
        If DDLPatentes.SelectedValue = "Su Elección" Then
            lblCrtlDDLPatentes.Visible = True
            PanelEdicion.Visible = False
            Exit Sub
        Else
            lblCrtlDDLPatentes.Visible = False
            PanelEdicion.Visible = True
        End If
        PanelEdicion.Visible = True
        db = Server.CreateObject("ADODB.Connection")
        rs = Server.CreateObject("ADODB.Recordset")
        Try
            db.open("Provider=Microsoft.jet.oledb.4.0;Data Source=" & base & ";")
            rs.open("SELECT * FROM Autos WHERE Patente = '" & DDLPatentes.SelectedValue & "' ORDER BY Patente", db)
            txtPatente.Text = rs.fields("Patente").value

            ViewState("Patente") = rs.fields("Patente").value

            txtMarca.Text = rs.fields("Marca").value() : ViewState("Marca") = rs.fields("Marca").value()
            txtModelo.Text = rs.fields("Modelo").value : ViewState("Modelo") = rs.fields("Modelo").value
            txtAño.Text = rs.fields("Año").value : ViewState("Año") = rs.fields("Año").value
            txtTitular.Text = rs.fields("Titular").value : ViewState("Titular") = rs.fields("Titular").value
            txtComisaria.Text = rs.fields("Comisaria").value : ViewState("Comisaria") = rs.fields("Comisaria").value
            txtEstado.Text = rs.fields("Estado").value : ViewState("Estado") = rs.fields("Estado").value

        Catch ex As Exception
            MsgBox("No puedo abrir tabla Autos " & vbCrLf & ex.Message, MsgBoxStyle.Critical, "Error proceso DDLPatentes_SelectedIndexChanged - Autos.aspx")
        End Try
        rs.close()
        db.close()
        db = Nothing
        rs = Nothing
    End Sub

    Protected Sub DDLPatenteEliminar_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLPatenteEliminar.SelectedIndexChanged
        If DDLPatenteEliminar.SelectedValue = "Su Elección" Then
            Exit Sub
        End If
    End Sub

    Protected Sub DDLCampos_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLCampos.SelectedIndexChanged
        If DDLCampos.SelectedValue = "Su Elección" Then
            Exit Sub
        End If
        db = Server.CreateObject("ADODB.Connection")
        rs = Server.CreateObject("ADODB.Recordset")
        Try
            db.open("Provider=Microsoft.jet.oledb.4.0;Data Source=" & base & ";")
            rs.open("SELECT DISTINCT " & DDLCampos.SelectedValue & " FROM Autos ORDER BY " & DDLCampos.SelectedValue & " ASC", db)
            DDLDatos.Items.Clear()
            DDLDatos.Items.Add("Su Elección")
            rs.movefirst()
            Do While Not rs.eof
                DDLDatos.Items.Add(rs.fields(0).value)
                rs.movenext()
            Loop
        Catch ex As Exception
            MsgBox("No puedo abrir recordset = SELECT DISTINCT " & DDLCampos.SelectedValue & " FROM Autos ORDER BY " & DDLCampos.SelectedValue & " ASC" & vbCrLf & ex.Message, MsgBoxStyle.Critical, "Error proceso DDLMarcas_SelectedIndexChanged - Autos.aspx")
        End Try
        rs.close()
        db.close()
        db = Nothing
        rs = Nothing
        txtSQL.Text = "SELECT * FROM Autos WHERE Autos." & DDLCampos.SelectedValue & " "
    End Sub

    Protected Sub DDLFiltro_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles DDLLogicos.SelectedIndexChanged, DDLDatos.SelectedIndexChanged
        If DDLCampos.SelectedValue = "Su Elección" Then
            MsgBox("Seleccione que campo usar." & vbCrLf, MsgBoxStyle.Critical, "Error proceso DDLFiltro_SelectedIndexChanged - Autos.aspx")
            lblCampo.Visible = True
            Exit Sub
        End If
        lblCampo.Visible = False
        If DDLLogicos.SelectedValue = "Su Elección" Then
            MsgBox("Seleccione que operador lógico usar." & vbCrLf, MsgBoxStyle.Critical, "Error proceso DDLFiltro_SelectedIndexChanged - Autos.aspx")
            lblLogico.Visible = True
            Exit Sub
        End If
        Select Case sender.id
            Case "DDLLogicos"
                lblLogico.Visible = False
                txtSQL.Text = txtSQL.Text & " " & DDLLogicos.SelectedValue
            Case "DDLDatos"
                lblDato.Visible = False
                txtSQL.Text = txtSQL.Text & " '" & DDLDatos.SelectedValue & "' "
        End Select
    End Sub

    Protected Sub btnAceptarFiltro_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAceptarFiltro.Click
        Cnx = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;Data Source=" & base & ";")
        Try
            Cnx.Open()
            Dadap = New OleDbDataAdapter(txtSQL.Text, Cnx)
            Dset = New Data.DataSet()
            Dadap.Fill(Dset, "MiTabla")
            GV2.DataSource = Dset
            GV2.DataMember = "MiTabla"
            GV2.DataBind()
            Cnx.Close()
        Catch ex As Exception
            MsgBox("No puedo ejecutar Filtro = " & txtSQL.Text & vbCrLf & ex.Message, MsgBoxStyle.Critical, "Error proceso btnAceptarFiltro_Click - Autos.aspx")
        End Try

    End Sub

    Protected Sub btnLimpiarSQL_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLimpiarSQL.Click
        txtSQL.Text = ""
        DDLCampos.SelectedIndex = 0
        DDLLogicos.SelectedIndex = 0
        DDLDatos.SelectedIndex = 0
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAceptarFiltro.Click

    End Sub

    Protected Sub CalcuWin(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim Proceso As New Diagnostics.Process()
        Proceso.StartInfo.FileName = "calc.exe"
        Proceso.StartInfo.Arguments = ""
        Proceso.Start()
    End Sub

End Class
